package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DB {
	public static String dbname = DBCommon.dbname ;
	public static String dbuser = DBCommon.dbuser ;
	public static String dbpassword = DBCommon.dbpassword ;
	public static String dbur1 = DBCommon.dbur1;
	
	public static com.alibaba.fastjson.JSONArray getList(String username, String netType, String type) {
		com.alibaba.fastjson.JSONArray rets = new com.alibaba.fastjson.JSONArray();
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
					}
					try {
//						String dbur1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Sing.mdb";
//						String dbur1 = "jdbc:mysql://47.96.8.102:3306/"+dbname+"?characterEncoding=UTF-8&useSSL=false";
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						//String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting  ";
						String sqlstr = "SELECT ID,title,content,isImediate,publishTime,photos,fengmian,videopath,portalphotopath from publish_setting where netType='"+netType+"' and username='"+username+"' and type='"+type+"' order by ID desc ";
						System.out.println(sqlstr);
						ResultSet set = sql.executeQuery(sqlstr);
						// System.out.println(set.next());

						while (set.next()) {
							com.alibaba.fastjson.JSONObject cObj = new com.alibaba.fastjson.JSONObject();
							String ID = set.getString(1);
							String title = set.getString(2);
							String content = set.getString(3);
							String isImediate = set.getString(4);
							String publishTime = set.getString(5);
							String photos = set.getString(6);
							String fengmian = set.getString(7);
							String videopath = set.getString(8);
							String portalphotopath = set.getString(9);

							cObj.put("ID", ID);
							cObj.put("title", title);
							cObj.put("content", content);
							cObj.put("isImediate", isImediate);
							cObj.put("publishTime", publishTime);
							cObj.put("photos", photos);
							cObj.put("fengmian", fengmian);
							cObj.put("videopath", videopath);
							cObj.put("portalphotopath", portalphotopath);

							rets.add(cObj);
						}
						set.close();
						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}

	public static boolean removeAll(String username, String netType, String type) {
		boolean rets = true;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
					}
					try {
						
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						String sqlstr = "delete from publish_setting where netType='"+netType+"' and username='"+username+"'  and type='"+type+"' ";
						System.out.println(sqlstr);
						sql.execute(sqlstr);

						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						rets = false;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static String queryCode(String ids) {
		String rets = null ;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
					}
					try {
						
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						String sqlstr = "SELECT code from publish_code where id='"+ids+"' ";
						System.out.println(sqlstr);
						ResultSet set = sql.executeQuery(sqlstr);
						// System.out.println(set.next());

						if (set.next()) {
							String codes = set.getString(1);
							rets = codes ;
						}
						set.close();
						conn.close();

						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						rets = null;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	
	public static String insertCode(String type, String photos) {
		String rets = null ;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
					}
					try {
						
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						String sqlstr = "insert into publish_code(type, photo) values('"+type+"','"+photos+"') ";
						System.out.println(sqlstr);
						sql.executeUpdate(sqlstr, Statement.RETURN_GENERATED_KEYS);
						// System.out.println(set.next());
						ResultSet set = sql.getGeneratedKeys(); 

						if (set.next()) {
							int keyValue = set.getInt(1); 
							rets = keyValue+"" ;
						}
						set.close();
						conn.close();

						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						rets = null;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}
	

	public static String insertResult(String username , String loginType, String netType , String result, String type  ) {
		String rets = null ;
		try {

			Connection conn = null;
			Statement sql = null;
			// set = null ;
			try {
				try {
					try {
						Class.forName("com.mysql.jdbc.Driver");
					} catch (ClassNotFoundException e) {
						System.out.println("" + e);
					}
					try {
						
						conn = DriverManager.getConnection(dbur1, dbuser, dbpassword);
						sql = conn.createStatement();
						String sqlstr = "insert into publish_result(username , loginType, netType , result, type  ) values('"+username+"','"+loginType+"','"+netType+"','"+result+"','"+type+"') ";
						System.out.println(sqlstr);
						sql.executeUpdate(sqlstr, Statement.RETURN_GENERATED_KEYS);
						// System.out.println(set.next());
						ResultSet set = sql.getGeneratedKeys(); 

						if (set.next()) {
							int keyValue = set.getInt(1); 
							rets = keyValue+"" ;
						}
						set.close();
						conn.close();

						conn.close();
					} catch (Exception el) {
						el.printStackTrace();
						rets = null;
					} finally {
						if (sql != null) {
							sql.close();
						}
						if (conn != null) {
							conn.close();
						}
						conn = null;
					}

				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (sql != null) {
						sql.close();
					}
					// if(set!=null){
					//
					// }
					if (conn != null) {
						conn.close();
					}
				}
			} catch (Exception e) {
				System.out.println("" + e);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return rets;
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		System.out.println(DB.queryCode("15"));
//		DB.queryCode("15");
	}

}
